package databaseTransaction;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import util.ConnectionConfig;
import util.TypeOfDatabaseSystem;

public class DatabaseTransaction {
	private String connURL;
	private String useName;
	private String pass;
	private ConnectionConfig connConfig;

	public DatabaseTransaction(ConnectionConfig connConfig) {
		this.connConfig = connConfig;
		this.connURL = "jdbc:"
				+ TypeOfDatabaseSystem.convertToString(connConfig
						.getTypeOfDBS()) + "://" + connConfig.getServerName()
				+ ':' + connConfig.getPort() + '/'
				+ connConfig.getDatabaseName();
		this.useName = connConfig.getUser();
		this.pass = connConfig.getPassword();
		System.out.println(this.connURL);
	}

	public Connection connect() throws SQLException {
		try {
			return DriverManager.getConnection(this.connURL, this.useName,
					this.pass);
		} catch (SQLException e) {
			e.printStackTrace();
			throw new SQLException("Can not connect to database system");
		}
	}

	public void closeConnection(Connection conn) throws SQLException {
		try {
			if (conn != null)
				conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new SQLException("Can not close connection");
		}
	}

	public void execute(String statement) throws SQLException {
		Statement stmt;
		Connection conn = connect();
		if (conn != null) {
			try {
				stmt = conn.createStatement();
				stmt.executeUpdate(statement);
			} catch (SQLException e) {
				e.printStackTrace();
				throw new SQLException("Can not execute update on database");
			}

			closeConnection(conn);
		}
	}

	public ResultSet excuteQuery(String statement, Connection conn,
			Statement stmt) throws SQLException {
		ResultSet rs = null;

		if (conn != null) {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(statement);

		}
		return rs;
	}

	public void excuteCreate(Table table) throws SQLException {
		String SQLString = "create table " + table.getName() + " (";

		List<TableField> allField = new ArrayList<TableField>(table.getId()); // combine
																				// fields
		allField.addAll(table.getTableFields());

		for (TableField tblField : allField) {
			SQLString = SQLString + tblField.getName() + " "
					+ tblField.parseDBType();

			if (tblField.getLength() != 0)
				SQLString = SQLString + "(" + tblField.getLength() + ")";

			SQLString = SQLString + ", ";
		}
		SQLString = SQLString.substring(0, SQLString.length() - 2);
		SQLString += ')';

		System.out.println(SQLString);
		this.execute(SQLString);
	}

	// get all id and set PK
	public void setPK(Table tbl) throws SQLException {
		if (tbl.getId().size() > 0) {
			String SQLString = "alter table " + tbl.getName()
					+ " add primary key (";
			for (TableField idField : tbl.getId()) {
				SQLString += idField.getName() + ", ";
			}
			SQLString = SQLString.substring(0, SQLString.length() - 2) + ")";
			System.out.println(SQLString);
			this.execute(SQLString);
		}

	}

	public void setAutoIncrease(String tbl, String tblField)
			throws SQLException {
		String SQLString = "ALTER TABLE " + tbl + " MODIFY COLUMN " + tblField
				+ " INT NOT NULL AUTO_INCREMENT";
		this.execute(SQLString);
	}

	public void setFK(String tbl, String field, String FK, String refTbl)
			throws SQLException {
		String SQLString = "ALTER TABLE " + tbl + " ADD CONSTRAINT " + field
				+ " FOREIGN KEY (" + field + ") REFERENCES " + refTbl + "("
				+ FK + ")";
		System.out.println(SQLString);
		this.execute(SQLString);
	}

	public boolean isTableExist(String tableName) throws SQLException {
		String SQLString = "select table_name from information_schema.tables where table_schema = \""
				+ connConfig.getDatabaseName()
				+ "\" and table_name = \""
				+ tableName + "\"";
		Connection conn = connect();
		Statement stmt = null;

		boolean result = false;
		try {
			stmt = conn.createStatement();
			ResultSet rs = this.excuteQuery(SQLString, conn, stmt);

			if (rs.next())
				result = true;

			if (stmt != null)
				stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();

		}

		return result;
	}
	
	public void dropExistTable(String tableName) throws SQLException {
		dropReferenceTables(tableName);
		String SQLString = "DROP TABLE " + tableName;
		this.execute(SQLString);
	}

	public void dropReferenceTables(String tableName) throws SQLException {
		String SQLString = "select COLUMN_NAME, TABLE_NAME from information_schema.KEY_COLUMN_USAGE "
				+ "where REFERENCED_TABLE_SCHEMA = \""
				+ connConfig.getDatabaseName()
				+ "\" and REFERENCED_TABLE_NAME = \"" + tableName + "\"";

		Connection conn = connect();
		Statement stmt = null;

		stmt = conn.createStatement();
		ResultSet rs = this.excuteQuery(SQLString, conn, stmt);

		while (rs.next()) {
			dropReference(rs.getString("TABLE_NAME"),
					rs.getString("COLUMN_NAME"));
		}
		try {
			if (stmt != null)
				stmt.close();
			closeConnection(conn);
		} catch (SQLException e) {
			e.printStackTrace();
			throw new SQLException("Can't close statement");
		}
	}

	public void dropReference(String tableName, String FK) throws SQLException {
		String SQLString = "ALTER TABLE " + tableName + " drop FOREIGN KEY "
				+ FK;
		this.execute(SQLString);
	}
	
	
	public void mergeExistTable(Table table) throws SQLException{
		List<String> currentFields = this.getColumns(table.getName());
		List<TableField> allField = new ArrayList<TableField>(table.getId()); // combine fields
		allField.addAll(table.getTableFields());
		for (TableField tblField : allField){
			if (!currentFields.contains(tblField.getName()))
				addField(tblField, table.getName());
		}
		
	}
	
	public void addField(TableField tblField, String tableName) throws SQLException{
		//ALTER TABLE `blahblahtable` ADD COLUMN `newcolumn` INT(12)
		String SQLString = "alter table " + tableName + " add column " + tblField.getName() + " " + tblField.parseDBType();
		if (tblField.getLength() != 0)
			SQLString = SQLString + "(" + tblField.getLength() + ")";
		this.execute(SQLString);
		
		System.out.println(SQLString);
	}
	
	public List<String> getColumns(String tableName)throws SQLException{
		List<String> columns = new ArrayList<String>();
		
		//	make SQL statemant
		String SQLString = "select column_name from information_schema.columns where table_name = \"" + tableName + "\" and table_schema = \"" + connConfig.getDatabaseName() + "\"";
		
		System.out.println(SQLString);
		
		//	init connection
		Connection conn = connect();
		Statement stmt = conn.createStatement();
		
		//	get result
		ResultSet rs = this.excuteQuery(SQLString, conn, stmt);
		while (rs.next()){
			columns.add(rs.getString("column_name"));
		}
		
		//	close connection
		try{
			if (stmt != null)
				stmt.close();
			closeConnection(conn);
		} catch (SQLException e) {
			e.printStackTrace();
			throw new SQLException("Can't close statement");
		}
		return columns;
	}
	
}